<?php
/**
 * Created by PhpStorm.
 * User: DELL
 * Date: 2018/5/2
 * Time: 22:16
 */

namespace app\admin\controller;
use app\admin\model\Sign;
use app\home\model\Test;
use think\Db;
use app\admin\model\WechatUser;
use app\home\model\Fenxiao;
use app\home\model\Stage;
use app\home\model\Gate;
use app\home\model\Xuexiao;
/**
 * Class Student
 * @package app\admin\controller
 * 学员分配
 */
class Student extends Admin
{
    /**
     * 首页
     */
    public function index(){
        $school = input('school');
        if (empty($school)){
            $map = ['dq_id' => ['neq',0]];
        }else{
            $map = ['dq_id' => ['neq',0] ,'dx' => ['like','%'.$school.'%']];
        }
        $y = input('y');
        $m = input('m');
        $list = Db::table('xuexiao')
            ->where($map)
            ->order('dx','asc')
            ->paginate(10,false,['query' => ['y'=> $y,'m' => $m,'school' => $school]]);
        $this->assign('list',$list);
        //获取全部共同数据年份
        $years = Sign::all(function($query){
            $query->group("FROM_UNIXTIME(create_time,'%Y')")->field("FROM_UNIXTIME(create_time,'%Y') as year");
        });
        $this->assign('years',$years);
        return $this->fetch();
    }
    /**
     * 分配学员
     */
    public function student()
    {
        $id = input('id');
        $vip = input('vip');
        if (empty($vip)) {
            $map = ['review_status' => 1, 'role' => 0, 'school_id' => $id];
        } else {
            if (substr($vip,0,1) === "@"){
                // 名字
                $vipp = substr($vip,1);
                $map = ['review_status' => 1, 'role' => 0, 'school_id' => $id, 'name' => ['like', '%' . $vipp . '%']];
            }else{
                // 账号
                $map = ['review_status' => 1, 'role' => 0, 'school_id' => $id, 'order_id' => ['like', '%' . $vip . '%']];
            }
        }
        $list = WechatUser::where($map)
            ->order('order_id')
            ->paginate(10,false,['query' => ['vip' => $vip]]);
        foreach ($list as $value){
            if ($value['words']){
                $value['words'] = json_decode($value['words']);
            }
            $value['class'] = "";
//            $res = Db::name('class')->where('openid',$value['openid'])->column('kid');
//            if (!empty($res)){
//                $value['class'] = $res;
//            }
        }
        //班型
//        $class = Db::name('skbx')->order('id','asc')->select();
        $this->assign('class',[]);
        $this->assign('list', $list);// 获取 用户列表
        return $this->fetch();
    }
    /**
     * 单词 操作 
     */
    public function play(){
        $id = input('id');
        $type = input('type/a');
        $res = WechatUser::where(['openid' => $id])->update(['words' => json_encode($type)]);
        if ($res){
            return $this->success('操作成功');
        }else{
            return ['code' => 0,'msg' => '未更新~操作失败'];
        }
    }
    /**
     * 单词 批量操作
     */
    public function plays(){
        $id = input('all/a');
        $type = input('type/a');
        $res = WechatUser::where(['openid' => ['in',$id]])->update(['words' => json_encode($type)]);
        if ($res){
            return $this->success('操作成功');
        }else{
            return ['code' => 0,'msg' => '未更新~操作失败'];
        }
    }
    /**
     * 班型操作
     */
//    public function cla(){
//        $id = input('id');
//        $class = input('class/a');
//        if (empty($class)){
//            // 空数组 没有班型 或者取消班型
//            $info = Db::name('class')->where('openid',$id)->find();
//            if (!empty($info)){
//                // 有值
//                Db::name('class')->where('openid',$id)->delete();
//            }
//            return $this->success('操作成功');
//        }else{
//            $data = [];
//            foreach ($class as $value){
//                array_push($data,['openid' => $id,'kid' => $value]);
//            }
//            $info = Db::name('class')->where('openid',$id)->find();
//            if (!empty($info)){
//                // 有值
//                Db::name('class')->where('openid',$id)->delete();
//            }
//            $res = Db::name('class')->insertAll($data);
//            if ($res){
//                return $this->success('操作成功');
//            }else{
//                return ['code' => 0,'msg' => '未更新~操作失败'];
//            }
//        }
//    }
    /**
     * 导出 Excel 服务
     */
    public function outpaper(){
        vendor("phpexcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory");
        vendor('phpexcel.Classes.PHPExcel');
        $school_id = input('school_id');
        $year = input('year');
        $month = input('month');
        //引用PHPExcel
        $PHPExcel = new \PHPExcel(); //实例化PHPExcel类，类似于在桌面上新建一个Excel表格
        $PHPSheet = $PHPExcel->getActiveSheet(); //获得当前活动sheet的操作对象
        //给当前活动sheet填充数据，数据填充是按顺序一行一行填充的，假如想给A1留空，可以直接setCellValue(‘A1’,’’);
        $cellName = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'];
        $title = ['姓名','VIP账号','本月签到数','签到排名','签到完成率','签到均值','签到方差','本月总结数','总结排名','总结完成率','总结均值','总结方差','本月测试数','测试排名','测试均值','测试方差']; //对应Excel标题
        //标题输出
        foreach ($title as $k => $v) {
            $PHPSheet->setCellValue($cellName[$k].'1',$v);
            $PHPSheet->getStyle($cellName[$k].'1')->getFont()->setSize(15);
            $PHPSheet->getColumnDimension($cellName[$k])->setWidth(15);
            $PHPSheet->getRowDimension(1)->setRowHeight(25);
            $PHPSheet->getStyle($cellName[$k])->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        }
        //内容输出
        $table = "info".$year;
        $one = strtotime(date('Y').'-'.$month.'-'.'01');
        $two = strtotime(date('Y').'-'.($month+1).'-'.'01');
        $res = Db::name($table)->where(['school_id' => $school_id,'mouth' => $month])->order(['sign_rank' => 'asc','summary_rank' => 'asc','test_rank' => 'asc'])->select();
        foreach ($res as $k => $v) {
            $sum_sign = $v['sign_s'];
            if ($v['sign_s'] == 0){
                $sum_sign = Db::name('sign')->where(['openid' => $v['openid'],'create_time' => ['between', [$one, $two]]])->count();
            }
            $sum_summary = $v['summary_s'];
            if ($v['summary_s'] == 0){
                $sum_summary = Db::name('summary')->where(['openid' => $v['openid'],'create_time' => ['between', [$one, $two]]])->count();
            }
            $sum_test = $v['test_s'];
            if ($v['test_s'] == 0){
                $sum_test = Db::name('test')->where(['openid' => $v['openid'],'test_time' => ['between', [$one, $two]],'status' => 0])->count();
            }
            $k = $k + 2;
            $sign_p = $v['sign_p']*100;
            $summary_p = $v['summary_p']*100;
            $PHPSheet->setCellValue($cellName[0] . $k, $v['name'])
            ->setCellValue($cellName[1] . $k, $v['order_id'])
                ->setCellValue($cellName[2] . $k, $sum_sign)
            ->setCellValue($cellName[3] . $k, $v['sign_rank'])
                ->setCellValue($cellName[4] . $k, $sign_p."%")
            ->setCellValue($cellName[5] . $k, $v['sign_ex'])
            ->setCellValue($cellName[6] . $k, $v['sign_dx'])
                ->setCellValue($cellName[7] . $k, $sum_summary)
            ->setCellValue($cellName[8] . $k, $v['summary_rank'])
                ->setCellValue($cellName[9] . $k, $summary_p."%")
            ->setCellValue($cellName[10] . $k, $v['summary_ex'])
            ->setCellValue($cellName[11] . $k, $v['summary_dx'])
                ->setCellValue($cellName[12] . $k, $sum_test)
                ->setCellValue($cellName[13] . $k, $v['test_rank'])
                ->setCellValue($cellName[14] . $k, $v['test_ex'])
                ->setCellValue($cellName[15] . $k, $v['test_dx']);
        }
        $school = Db::table('xuexiao')->where('dx_id',$school_id)->find();
        if ($school){
            $s_name = $school['dx'];
        }else{
            $s_name = '学校（暂无）';
        }
        $fileName = $s_name.$year."-".$month;
        // 设置下载头部
        $ua = $_SERVER["HTTP_USER_AGENT"];
        $encoded_filename = urlencode($fileName);
        $encoded_filename = str_replace("+", "%20", $encoded_filename);
        header('Content-Type: application/octet-stream');
        if (preg_match("/MSIE/", $ua)) {
            header('Content-Disposition: attachment; filename="'.$encoded_filename.'.xls');
        } else if (preg_match("/Firefox/", $ua)) {
            header('Content-Disposition: attachment; filename*="utf8\'\''.$fileName.'.xls');
        } else {
            header('Content-Disposition: attachment; filename="'.$fileName.'.xls');
        }
        header('Content-Type: application/vnd.ms-excel;');
        header('Cache-Control: max-age=0');
        // 输出下载文件
        $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }
    /**
     * 导出 Excel 测试
     */
    public function outpapers(){
        vendor("phpexcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory");
        vendor('phpexcel.Classes.PHPExcel');
        $school_id = input('school_id');
        $year = input('year');
        $before = mktime(0, 0, 0, 01, 01, $year);
        $after = mktime(0, 0, 0, 01, 01, $year+1);
        //引用PHPExcel
        $PHPExcel = new \PHPExcel(); //实例化PHPExcel类，类似于在桌面上新建一个Excel表格
        $PHPSheet = $PHPExcel->getActiveSheet(); //获得当前活动sheet的操作对象
        //给当前活动sheet填充数据，数据填充是按顺序一行一行填充的，假如想给A1留空，可以直接setCellValue(‘A1’,’’);
        $cellName = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'];
        $title = ['姓名','VIP账号','测试详情']; //对应Excel标题
        //标题输出
        foreach ($title as $k => $v) {
            $PHPSheet->setCellValue($cellName[$k].'1',$v);
            $PHPSheet->getStyle($cellName[$k].'1')->getFont()->setSize(15);
            $PHPSheet->getColumnDimension($cellName[$k])->setWidth(15);
            $PHPSheet->getRowDimension(1)->setRowHeight(25);
            $PHPSheet->getStyle($cellName[$k])->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        }
        //内容输出
        $res = Db::name("wechat_user")->where(['school_id' => $school_id,'role' => 0,'review_status' => 1])->order(['party' => 'desc','name' => 'asc'])->field('openid,name,order_id')->select();
        foreach ($res as $k => $v) {
            $k = $k + 2;
            $PHPSheet->setCellValue($cellName[0] . $k, $v['name'])
                ->setCellValue($cellName[1] . $k, $v['order_id']);
            $Test = Test::where(['openid' => $v['openid'],'test_time' =>['between',[$before,$after]],'status' => 0])->order('test_time','desc')->field('score,code,test_time')->group('code')->select();
            foreach ($Test as $key => $value){
                $y = $key + 2;
                $class = '*空*';  // 阶段
                $class1 = '*空*';  // 大类
                $class2 = '*空*';  // 项目大类
                $class3 = '*空*';  // 一级项目
                $class4 = '*空*';  // 二级项目
                $paper = Fenxiao::where('paper_id',$value['code'])->find();
                if ($paper){
                    $stage = Stage::where('id',$paper['stage_id'])->value('name');
                    $class = iconv("gbk", "utf-8//ignore", $stage);  // 阶段
                    $gate = Gate::where('id',$paper['project_id'])->field('class1,class2,class3,class4')->find();
                    $class1 = iconv("gbk", "utf-8//ignore", $gate['class1']);  // 大类
                    $class2 = iconv("gbk", "utf-8//ignore", $gate['class2']);  // 项目大类
                    $class3 = iconv("gbk", "utf-8//ignore", $gate['class3']);  // 一级项目
                    $class4 = iconv("gbk", "utf-8//ignore", $gate['class4']);  // 二级项目
                }
                $PHPSheet->setCellValue($cellName[$y] . $k, $value['score'].'/'.$class.'-'.$class1.'-'.$class2.'-'.$class3.'-'.$class4.'/'.date('Y-m-d H:i',$value['test_time']));
            }
        }
        $school = Xuexiao::where('dx_id',$school_id)->field('dx')->find();
        if ($school){
            $s_name = $school['dx'];
        }else{
            $s_name = '学校（暂无）';
        }
        $fileName = $s_name.'-'.$year;
        // 设置下载头部
        $ua = $_SERVER["HTTP_USER_AGENT"];
        $encoded_filename = urlencode($fileName);
        $encoded_filename = str_replace("+", "%20", $encoded_filename);
        header('Content-Type: application/octet-stream');
        if (preg_match("/MSIE/", $ua)) {
            header('Content-Disposition: attachment; filename="'.$encoded_filename.'.xls');
        } else if (preg_match("/Firefox/", $ua)) {
            header('Content-Disposition: attachment; filename*="utf8\'\''.$fileName.'.xls');
        } else {
            header('Content-Disposition: attachment; filename="'.$fileName.'.xls');
        }
        header('Content-Type: application/vnd.ms-excel;');
        header('Cache-Control: max-age=0');
        // 输出下载文件
        $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }
}